﻿using OfficeOpenXml;
using SubstrateHelper.Models;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Threading.Tasks;

namespace SubstrateHelper.Helpers
{
    public class ExcelFileReplacer
    {
        public static List<TemplateReplacement> GetReplacements(string templateFile)
        {
            ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
            string file = Path.Combine(Directory.GetCurrentDirectory(), "Templates\\TemplateReplacements.xlsx");
            FileInfo fileInfo = new FileInfo(file);
            ExcelPackage excelPackage = new ExcelPackage(fileInfo);
            string templateFileName = Path.GetFileNameWithoutExtension(templateFile);
            string tableName;
            try
            {
                excelPackage.Workbook.Worksheets.Where(s => s.Name == templateFileName).First();
                tableName = templateFileName;//专用替换表

            }
            catch
            {
                tableName = "ScheduleTemplate";//默认替换表
            }

            var sheet = excelPackage.Workbook.Worksheets[tableName];
            List<TemplateReplacement> replacements = new List<TemplateReplacement>();

            var tables = EPPlusBuilder.Parse(excelPackage, true);
            foreach (var table in tables.Tables)
            {
                if (table.Name == tableName)
                {
                    foreach (var row in table.Body)
                    {
                        replacements.Add(new TemplateReplacement()
                        {
                            Item = row.Cells[0].Value,
                            Row = int.Parse(row.Cells[1].Value),
                            Col = int.Parse(row.Cells[2].Value)
                        });
                    }
                    break;
                }
            }

            return replacements;
            
        }

        public static void Replace(ExcelWorksheet sheet, List<TemplateReplacement> replacements, string item, string value)
        {
            ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
            try
            {
                var rps = replacements.Where(r => r.Item == item).ToList();
                foreach (var r in rps)
                {
                    sheet.Cells[r.Row, r.Col].Value = value;
                }
            }
            catch (Exception ex)
            {

            }
        }
    }
}
